﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CarLoanSystem.DAL
{
    public class ReportInfoDAL : DBBase
    {
        public static DataTable LoadAllLoan(string loanId = "", string name = "", string lastName = "")
        {
            try
            {
                string sqlQuery = @"SELECT dbo.LOAN.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME,
                      dbo.LOAN.PRINCIPLE, dbo.CAR_BRAND.CAR_BRAND_NAME,dbo.CAR_MODEL.CAR_MODEL_NAME, dbo.CAR_INFO.CAR_TYPE_ENGINE,
                      dbo.LOAN.LOAN_STATUS FROM dbo.LOAN INNER JOIN
                      dbo.CAR_INFO ON dbo.LOAN.CAR_ID = dbo.CAR_INFO.CAR_ID INNER JOIN
                      dbo.CAR_BRAND INNER JOIN
                      dbo.CAR_MODEL ON dbo.CAR_BRAND.CAR_BRAND_ID = dbo.CAR_MODEL.CAR_BRAND_ID ON 
                      dbo.CAR_INFO.CAR_MODEL_ID = dbo.CAR_MODEL.CAR_MODEL_ID AND dbo.CAR_INFO.CAR_BRAND_ID = dbo.CAR_BRAND.CAR_BRAND_ID INNER JOIN
                      dbo.CUSTOMER ON dbo.LOAN.CUSTOMER_ID = dbo.CUSTOMER.CUSTOMER_ID WHERE 1=1";

                if (loanId != "")
                {
                    sqlQuery += " and [LOAN_ID] = " + loanId;
                }
                if (name != "")
                {
                    sqlQuery += " and [CUSTOMER_NAME] like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " and [CUSTOMER_LASTNAME] like '%" + lastName + "%'";
                }

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadSlip(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT dbo.PAYMENTS.PAYMENTS_ID, dbo.PAYMENTS.LOAN_ID,
                                                dbo.PAYMENTS.EMPLOYEE_ID, dbo.PAYMENTS.PAYMENTS_DUE_DATE, 
                                                dbo.PAYMENTS.PAYMENTS_DATE, dbo.PAYMENTS.PAYMENTS_STATUS, dbo.LOAN.PAY_MONTH, 
                                                dbo.CUSTOMER.CUSTOMER_NAME + ' ' + dbo.CUSTOMER.CUSTOMER_LASTNAME AS NAME,
                                                dbo.CUSTOMER.CUSTOMER_PERSONAL_ID
                                                FROM dbo.LOAN INNER JOIN
                                                dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID INNER JOIN
                                                dbo.CUSTOMER ON dbo.LOAN.CUSTOMER_ID = dbo.CUSTOMER.CUSTOMER_ID
                                                WHERE [PAYMENTS_ID] = @PAYMENTS_ID", DBBase.con);
                command.Parameters.AddWithValue("@PAYMENTS_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadSlipAll(int[] arrayId)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                string sqlQuery = @"SELECT dbo.PAYMENTS.PAYMENTS_ID, dbo.PAYMENTS.LOAN_ID,
                                                dbo.PAYMENTS.EMPLOYEE_ID, dbo.PAYMENTS.PAYMENTS_DUE_DATE, 
                                                dbo.PAYMENTS.PAYMENTS_DATE, dbo.PAYMENTS.PAYMENTS_STATUS, dbo.LOAN.PAY_MONTH, 
                                                dbo.CUSTOMER.CUSTOMER_NAME + ' ' + dbo.CUSTOMER.CUSTOMER_LASTNAME AS NAME,
                                                dbo.CUSTOMER.CUSTOMER_PERSONAL_ID
                                                FROM dbo.LOAN INNER JOIN
                                                dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID INNER JOIN
                                                dbo.CUSTOMER ON dbo.LOAN.CUSTOMER_ID = dbo.CUSTOMER.CUSTOMER_ID
                                                WHERE [PAYMENTS_ID] in (";
                for (int i = 0; i < arrayId.Length; i++)
                {
                    if (i != arrayId.Length - 1)
                    {
                        sqlQuery += arrayId[i] + ",";
                    }
                    else
                    {
                        sqlQuery += arrayId[i] + ")";
                    }
                }

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllSaleLoan(string groupId = "", string id = "", string name = "", string lastName = "")
        {
            try
            {
                string sqlQuery = @"SELECT dbo.EMPLOYEE.EMPLOYEE_ID, dbo.EMPLOYEE.EMPLOYEE_GROUP, dbo.EMPLOYEE.EMPLOYEE_NAME, 
                dbo.EMPLOYEE.EMPLOYEE_LASTNAME, SUM(dbo.LOAN.PRINCIPLE) AS PRINCIPLE FROM dbo.EMPLOYEE INNER JOIN
                dbo.SCHEDULE ON dbo.EMPLOYEE.EMPLOYEE_ID = dbo.SCHEDULE.EMPLOYEE_ID INNER JOIN dbo.LOAN ON dbo.SCHEDULE.LOAN_ID = dbo.LOAN.LOAN_ID
                WHERE (dbo.EMPLOYEE.EMPLOYEE_POSITION = 's') AND dbo.SCHEDULE.SCHEDULE_STATUS = 'I' ";

                if (groupId != "")
                {
                    sqlQuery += " and dbo.EMPLOYEE.EMPLOYEE_GROUP = '" + groupId + "'";
                }
                if (id != "")
                {
                    sqlQuery += " and dbo.EMPLOYEE.EMPLOYEE_ID = " + id;
                }
                if (name != "")
                {
                    sqlQuery += " and dbo.EMPLOYEE.EMPLOYEE_NAME like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " and dbo.EMPLOYEE.EMPLOYEE_LASTNAME like '%" + lastName + "%'";
                }

                sqlQuery += @" GROUP BY dbo.EMPLOYEE.EMPLOYEE_GROUP, dbo.EMPLOYEE.EMPLOYEE_NAME, 
                dbo.EMPLOYEE.EMPLOYEE_LASTNAME, dbo.EMPLOYEE.EMPLOYEE_ID";

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllPayment(string loanId = "", string name = "", string lastName = "")
        {
            try
            {
                string sqlQuery = @"Select MS.LOAN_ID, MS.CUSTOMER_NAME, MS.CUSTOMER_LASTNAME, MS.PAY_MONTH, 
                      MS.DEBT_MONTH_REST , DT1.MONTH_QTY ,DT2.MONTH_QTY_ALL from
                    (SELECT dbo.LOAN.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME, dbo.LOAN.PAY_MONTH, 
                      dbo.LOAN.DEBT_MONTH_REST FROM dbo.INTERREST_INFO INNER JOIN
                      dbo.DOWN_PAYMENT_INFO ON dbo.INTERREST_INFO.DOWN_PAYMENT_ID = dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_ID INNER JOIN
                      dbo.CUSTOMER INNER JOIN
                      dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID ON dbo.INTERREST_INFO.INTERREST_ID = dbo.LOAN.INTERREST_ID
                    GROUP BY dbo.LOAN.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME, dbo.LOAN.PAY_MONTH, dbo.LOAN.DEBT_MONTH_REST) MS
                    FULL JOIN (SELECT ISNULL(COUNT(PAYMENTS.PAYMENTS_ID), 0) AS MONTH_QTY,PAYMENTS.LOAN_ID FROM PAYMENTS 
                    WHERE PAYMENTS.PAYMENTS_STATUS = 'I'
                    GROUP BY PAYMENTS.LOAN_ID) DT1 ON MS.LOAN_ID = DT1.LOAN_ID INNER JOIN
                    (SELECT ISNULL(COUNT(PAYMENTS.PAYMENTS_ID), 0) AS MONTH_QTY_ALL,PAYMENTS.LOAN_ID FROM PAYMENTS 
                    GROUP BY PAYMENTS.LOAN_ID) DT2 ON MS.LOAN_ID = DT2.LOAN_ID WHERE 1=1";

                if (loanId != "")
                {
                    sqlQuery += " and MS.LOAN_ID = " + loanId;
                }
                if (name != "")
                {
                    sqlQuery += " and MS.CUSTOMER_NAME like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " and MS.CUSTOMER_LASTNAME like '%" + lastName + "%'";
                }

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllIncom(string startYear = "", string endYear = "")
        {
            try
            {
                string sqlQuery = @" Select * ,(Case when (PAY - PRINCIPLE ) < 0 then 0 Else (PAY - PRINCIPLE ) End ) AS PROFIT,
                (Case when (PRINCIPLE - PAY) < 0 then 0 Else (PRINCIPLE - PAY) End ) AS LOSS from( select YEAR1,PRINCIPLE, SUM(PAY) AS PAY from(
                SELECT YEAR(LOAN_SUCCESS_DATE) AS YEAR1, SUM(PRINCIPLE) AS PRINCIPLE FROM dbo.LOAN WHERE (LOAN_STATUS = 'I')
                GROUP BY YEAR(LOAN_SUCCESS_DATE)) MS FULL JOIN (SELECT SUM(dbo.LOAN.PAY_MONTH) AS PAY FROM  dbo.LOAN INNER JOIN
                dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID WHERE (dbo.PAYMENTS.PAYMENTS_STATUS = 'I')
                GROUP BY dbo.PAYMENTS.LOAN_ID, dbo.PAYMENTS.PAYMENTS_STATUS) DT ON MS.YEAR1 <> DT.PAY WHERE 1=1";

                if (startYear != "" && endYear != "")
                {
                    sqlQuery += " AND YEAR1 BETWEEN '" + startYear + "' AND '" + endYear + "'";
                }
                else if (startYear != "")
                {
                    sqlQuery += " AND YEAR1 >= '" + startYear + "'";
                }

                sqlQuery += "GROUP BY YEAR1,PRINCIPLE ) MM";

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllDept(string empId = "", string name = "", string lastName = "")
        {
            try
            {
                string sqlQuery = @"SELECT MS.EMPLOYEE_ID,MS.EMPLOYEE_NAME,MS.EMPLOYEE_LASTNAME,MS.WORK_ALL_AMOUNT,DT1.WORK_AMOUNT1,DT2.WORK_AMOUNT_COMPLETE1
                FROM (SELECT dbo.EMPLOYEE.EMPLOYEE_ID, dbo.EMPLOYEE.EMPLOYEE_NAME, dbo.EMPLOYEE.EMPLOYEE_LASTNAME, 
                COUNT(dbo.SCHEDULE.SCHEDULE_ID) AS WORK_ALL_AMOUNT
                FROM dbo.EMPLOYEE INNER JOIN dbo.SCHEDULE ON dbo.EMPLOYEE.EMPLOYEE_ID = dbo.SCHEDULE.EMPLOYEE_ID
                WHERE dbo.EMPLOYEE.EMPLOYEE_POSITION = 'd' AND dbo.SCHEDULE.SCHEDULE_JOB = 'D'
                GROUP BY dbo.EMPLOYEE.EMPLOYEE_ID, dbo.EMPLOYEE.EMPLOYEE_NAME, dbo.EMPLOYEE.EMPLOYEE_LASTNAME) MS
                FULL JOIN (SELECT ISNULL(COUNT([SCHEDULE_ID]),0) AS WORK_AMOUNT1 ,EMPLOYEE_ID 
                FROM [CAR_LOAN].[dbo].[SCHEDULE]
                WHERE SCHEDULE.SCHEDULE_JOB = 'D' AND SCHEDULE.SCHEDULE_STATUS = 'A'
                GROUP BY EMPLOYEE_ID) DT1 ON MS.EMPLOYEE_ID = DT1.EMPLOYEE_ID
                FULL JOIN (SELECT ISNULL(COUNT([SCHEDULE_ID]),0) AS WORK_AMOUNT_COMPLETE1 ,EMPLOYEE_ID 
                FROM [CAR_LOAN].[dbo].[SCHEDULE]
                WHERE SCHEDULE.SCHEDULE_JOB = 'D' AND SCHEDULE.SCHEDULE_STATUS = 'I'
                GROUP BY EMPLOYEE_ID) DT2 ON MS.EMPLOYEE_ID = DT2.EMPLOYEE_ID WHERE 1=1";

                if (empId != "")
                {
                    sqlQuery += " and MS.EMPLOYEE_ID = " + empId;
                }
                if (name != "")
                {
                    sqlQuery += " and MS.EMPLOYEE_NAME like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " and MS.EMPLOYEE_LASTNAME like '%" + lastName + "%'";
                }

                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
}
